/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package src.com.dados.pedagogico;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author Patricia
 */
public class ProfessorDao {
    
    public ProfessorDao (){
    }
   
    public void inserirProfessor(String nomeCompleto,String rg, String cpf,String dtNascimento,String endereco,String bairro,String numero,String estado,String cidade,String telefone,String celular,String email,String formacao,String idiomas,String dtAdmissao,String dtDemissao ) throws SQLException {
        Connection conexao;
        String in_pessoa =  "INSERT INTO `bdeng2`.`pes_pessoa` (`pes_nome`, `pes_rg`, `pes_cpf`, `pes_dt_nascimento`, `pes_endereco`, `pes_numero`, `pes_bairro`, `pes_cidade`, `pes_estado`, `pes_email`, `pes_telefone`, `pes_celular`) VALUES('" + nomeCompleto + "', '"
                + rg + "', '" + cpf + "', '" + dtNascimento + "', '" + endereco + "', " +numero +", '" + bairro +"', '" + cidade +"', '" + estado +"', '" + email +"', '" + telefone +"', '" + celular +"');";
         
               
         System.out.print(in_pessoa);
         
         //System.out.print(in_professor);
         
        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        
         Statement stmt2 = (Statement) conexao.createStatement();
         stmt2.execute(in_pessoa);
         
         String in_professor =  "INSERT INTO `bdeng2`.`prof_professor` (`prof_idioma`, `prof_formacao`, `pes_pessoa_pes_id`, `prof_dt_admissao`, `prof_dt_demissao`) VALUES  ('" + idiomas + "', '" + formacao + "'," + id_pessoa(nomeCompleto)+", '" + dtAdmissao + "', '" + dtDemissao + "');";
          
         
         Statement stmt3 = (Statement) conexao.createStatement();
         stmt3.execute(in_professor);
         
         System.out.print(in_professor);
         
         conexao.close();
    }
    
        public String id_professor(String nome) throws SQLException {
        Connection conexao;
        String result = null;
       
        String select_idProfessor = "SELECT prof_id FROM prof_professor,pes_pessoa where pes_pessoa_pes_id = pes_id and pes_nome like '"+nome+"';";
        

        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_idProfessor);
        ResultSet rs = stmt.executeQuery();



        while (rs.next()) {
            result = rs.getString(1);
        }

        conexao.close();
        return result;


    }
   
         public String id_pessoa(String nome) throws SQLException {
        Connection conexao;
        String result = null;
       
        String select_idPessoa = "SELECT pes_id FROM pes_pessoa WHERE pes_nome like '"+nome+"';";
        

        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_idPessoa);
        ResultSet rs = stmt.executeQuery();



        while (rs.next()) {
            result = rs.getString(1);
        }

        conexao.close();
        return result;


    }
    
}
